Generated code - Transactions, SelfServicing

Preface

SelfServicing supports ADO.NET transactions and System.Transactions (distributed) transactions. SelfServicing automatically uses ADO.NET transactions for recursive saves and save/delete actions for collections of entities so you don't have to start/commit transactions yourself if you persist entity changes.

Normal native database transactions

Native database transactions are provided by ADO.NET; it's a part of an ADO.NET connection object and that transaction object can be used to execute all database statements in that transaction if that connection is used. LLBLGen Pro's native database transactions work the same for you: you create an instance of the Transaction class and add the objects that should participate (use) that transaction to that transaction object. As of that moment the actions you perform on those objects are executed in the transaction of that transaction object.

An example will help illustrate the usage of the Transaction class. Let's add a new order with an order row to the persistent storage for the customer "CHOPS". Because we'll add two entities, we will use a transaction to make sure that when the second save fails, the first is rolled back.

note Note:
The example below is just to show you how to use the Transaction object as it doesn't use the recursive save functionality build into the code. As recursive saves already use an ADO.NET transaction internally, the example would have been much smaller without the external transaction. To illustrate this, the same example is also listed using recursive saves. It also doesn't use the FK-PK synchronization functionality which synchronizes Foreign key fields with their Primary key field's value, after a save, for example when newOrderRow is added to newOrder.OrderDetails, which will sync the new PK value of newOrder.OrderID with newOrderRow.OrderID once newOrder has been saved. See the recursive save example below for an example of that.

First, let's see the code: (it assumes the entity data for "CHOPS" is already loaded in the object customer. The data is rather bogus, it's for illustration purposes only).

// [C#]
// Create the transaction object, pass the isolation level and give it a name
Transaction transactionManager = new Transaction(IsolationLevel.ReadCommitted, "Test");

// create a new order and then 2 new order rows. 
try
{
	// create new order entity. Use data from the object 'customer'
	OrderEntity newOrder = new OrderEntity();
	
	// set the customer reference, which will sync FK-PK values.
	// (newOrder.CustomerID = customer.CustomerID)
	newOrder.Customer = customer;
	
	newOrder.EmployeeID = 1;
	newOrder.Freight = 10;
	newOrder.OrderDate = DateTime.Now.AddDays(-3.0);
	newOrder.RequiredDate = DateTime.Now.AddDays(3.0);
	newOrder.ShipAddress = customer.Address;
	newOrder.ShipCity = customer.City;
	newOrder.ShipCountry = customer.Country;
	newOrder.ShipName = "The Bounty";
	newOrder.ShippedDate = DateTime.Now;
	newOrder.ShipRegion = customer.Region;
	newOrder.ShipVia = 1;
	newOrder.ShipPostalCode = customer.PostalCode;

	// add this new order to the transaction so actions will run inside the transaction
	transactionManager.Add(newOrder);

	// save the new order. When this fails, will throw exception which will terminate transaction.
	newOrder.Save();

	// Create new order row.
	OrderDetailsEntity newOrderRow = new OrderDetailsEntity();
	newOrderRow.OrderID = newOrder.OrderID;	// will refetch order from persistent storage.
	newOrderRow.Discount = 0;
	newOrderRow.ProductID = 10;
	newOrderRow.Quantity = 200;
	newOrderRow.UnitPrice = 31;

	// add this new orderrow to the transaction
	transactionManager.Add(newOrderRow);

	// save the new orderrow. When this fails, will throw exception which will terminate transaction.
	newOrderRow.Save();

	// done, commit the transaction
	transactionManager.Commit();
}
catch(Exception)
{
	// abort, roll back the transaction
	transactionManager.Rollback();
	throw;
}
finally
{
	// clean up. Necessary action.
	transactionManager.Dispose();
}
' [VB.NET]
' Create the transaction object, pass the isolation level and give it a name
Dim transactionManager As new Transaction(IsolationLevel.ReadCommitted, "Test")

' create a new order and then 2 new order rows. 
Try
	' create new order entity. Use data from the object 'customer'.
	Dim newOrder As New OrderEntity()
	
	' set the customer reference, which will sync FK-PK values 
	' (newOrder.CustomerID = customer.CustomerID)
	newOrder.Customer = customer
	
	newOrder.EmployeeID = 1
	newOrder.Freight = 10
	newOrder.OrderDate = DateTime.Now.AddDays(-3.0)
	newOrder.RequiredDate = DateTime.Now.AddDays(3.0)
	newOrder.ShipAddress = customer.Address
	newOrder.ShipCity = customer.City
	newOrder.ShipCountry = customer.Country
	newOrder.ShipName = "The Bounty"
	newOrder.ShippedDate = DateTime.Now
	newOrder.ShipRegion = customer.Region
	newOrder.ShipVia = 1
	newOrder.ShipPostalCode = customer.PostalCode

	' add this new order to the transaction so actions will run inside the transaction
	transactionManager.Add(newOrder)

	' save the new order. When this fails, will throw exception which will terminate transaction.
	newOrder.Save()

	' Create new order row.
	Dim newOrderRow As New OrderDetailsEntity()
	newOrderRow.OrderID = newOrder.OrderID	' will refetch order from persistent storage.
	newOrderRow.Discount = 0
	newOrderRow.ProductID = 10
	newOrderRow.Quantity = 200
	newOrderRow.UnitPrice = 31

	' add this new orderrow to the transaction
	transactionManager.Add(newOrderRow)

	' save the new orderrow. When this fails, will throw exception which will terminate transaction.
	newOrderRow.Save()

	' done, commit the transaction
	transactionManager.Commit()
Catch
	' abort, roll back the transaction
	transactionManager.Rollback()
	Throw
Finally
	' clean up. Necessary action.
	transactionManager.Dispose()
End Try

First a Transaction object is created. As soon as you instantiate the object, a database connection is open and usable. This is also the reason why you have to include a finally clause and have to call Dispose() when the transaction is no longer needed. If you want an entity or entity collection object (and all its objects inside it) to participate in a transaction, add it to the particular transaction object with the Add() method. You can only add objects that can execute data modification statements like an entity object or an entity collection object.

When you add an entity or entity collection object to a Transaction object, from that moment on its logic will use the connection of the transaction object to work with the database, which automatically makes sure the database statements executed will be run inside the transaction itself. The transaction object will take care of every other overhead like notifying objects when a transaction has been finished or aborted. If a transaction is aborted (Rollback() is called) or Committed (Commit() is called), all objects participating in the transaction are automatically removed from the transaction so you don't have to do that yourself.

It's best practice to embed the usage of the transaction in a try/catch/finally statement as it is done in the example above, so you can make sure when something fails during the usage of the transaction: everything is rolled back or at the end and everything is committed correctly.

Using recursive save functionality
The same functionality, but now implemented using the recursive save functionality build into the generated code. As you can see, no external transaction is used, because the code starts a new transaction internally. You can always add the entities to an existing transaction, which then makes the save actions take place inside that transaction. Recursive saves always create a normal ADO.NET transaction with isolation level ReadComitted. 

// [C#]
// create a new order and then 2 new order rows. 
// create new order entity. Use data from the object 'customer'
OrderEntity newOrder = new OrderEntity();

// set the customer reference, which will sync FK-PK values.
// (newOrder.CustomerID = customer.CustomerID). You also could have said:
// newOrder.CustomerID = customer.CustomerID;
// or
// newOrder.CustomerID = _someVariable;
newOrder.Customer = customer;

newOrder.EmployeeID = 1;
newOrder.Freight = 10;
newOrder.OrderDate = DateTime.Now.AddDays(-3.0);
newOrder.RequiredDate = DateTime.Now.AddDays(3.0);
newOrder.ShipAddress = customer.Address;
newOrder.ShipCity = customer.City;
newOrder.ShipCountry = customer.Country;
newOrder.ShipName = "The Bounty";
newOrder.ShippedDate = DateTime.Now;
newOrder.ShipRegion = customer.Region;
newOrder.ShipVia = 1;
newOrder.ShipPostalCode = customer.PostalCode;

// Create new order row.
OrderDetailsEntity newOrderRow = new OrderDetailsEntity();
newOrderRow.Discount = 0;
newOrderRow.ProductID = 10;
newOrderRow.Quantity = 200;
newOrderRow.UnitPrice = 31;
// make sure the OrderID fields are synchronized when
// newOrder is saved.
newOrder.OrderDetails.Add(newOrderRow);

// save the new order, recursively. This will first save customer
// if that's changed, then newOrder, then sync newOrder.OrderID with newOrderRow.OrderID
// and then save newOrderRow. The complete Save action is done inside an ADO.NET transaction.
newOrder.Save(true);
' [VB.NET]
' create a new order and then 2 new order rows. 
' create new order entity. Use data from the object 'customer'
Dim newOrder As New OrderEntity()
' set the customer reference, which will sync FK-PK values.
' (newOrder.CustomerID = customer.CustomerID). You also could have said:
' newOrder.CustomerID = customer.CustomerID
' or
' newOrder.CustomerID = _someVariable
newOrder.Customer = customer
newOrder.EmployeeID = 1
newOrder.Freight = 10
newOrder.OrderDate = DateTime.Now.AddDays(-3.0)
newOrder.RequiredDate = DateTime.Now.AddDays(3.0)
newOrder.ShipAddress = customer.Address
newOrder.ShipCity = customer.City
newOrder.ShipCountry = customer.Country
newOrder.ShipName = "The Bounty"
newOrder.ShippedDate = DateTime.Now
newOrder.ShipRegion = customer.Region
newOrder.ShipVia = 1
newOrder.ShipPostalCode = customer.PostalCode

' Create new order row.
Dim newOrderRow As New OrderDetailsEntity()
newOrderRow.Discount = 0
newOrderRow.ProductID = 10
newOrderRow.Quantity = 200
newOrderRow.UnitPrice = 31
' make sure the OrderID fields are synchronized when
' newOrder is saved.
newOrder.OrderDetails.Add(newOrderRow)

' save the new order, recursively. This will first save customer
' if that's changed, then newOrder, then sync newOrder.OrderID with newOrderRow.OrderID
' and then save newOrderRow. The complete Save action is done inside an ADO.NET transaction.
newOrder.Save(True)

Transaction savepoints

Most databases support transaction save-points. Transaction save-points make it possible to do fine grained transaction control on a semi-nested level. This can be helpful as ADO.NET doesn't support nested transactions. Save-points let you define a point in a transaction to which you can roll back, without rolling back the complete transaction. This can be handy if you for example have saved some entities in a transaction which were saved OK, and another one fails, however the failure of that save shouldn't terminate the whole transaction, just roll back the transaction to a given point in the transaction.

The following example illustrates the save-point functionality. It first saves a new Address entity and after that it saves the transaction. It then saves a new Customer entity but takes into account that this can fail. If it does, it should roll back to the save-point set before the second save, so it avoids rolling back the complete transaction. 

//C#
' Create the transaction object, pass the isolation level and give it a name
Transaction transactionManager = new Transaction(IsolationLevel.ReadCommitted, "SavepointRollback");

try
{
	// first save a new address
	AddressEntity newAddress = new AddressEntity();
	// ... fill the address entity with values

	transactionManager.Add(newAddress);
	
	// save it.
	newAddress.Save();

	// create a transaction save point
	transactionManager.Save("SavepointAddress");

	// save a new customer
	CustomerEntity newCustomer = new CustomerEntity();
	// ... fill the customer entity with values
	newCustomer.VisitingAddress = newAddress;
	newCustomer.BillingAddress = newAddress;
	
	transactionManager.Add(newCustomer);
	
	try
	{
		newCustomer.Save();
	}
	catch(Exception ex)
	{
		// something was wrong. 
		// ... handle ex here.

		// roll back to savepoint.
		transactionManager.Rollback("SavepointAddress");
	}
	
	// commit the transaction. If the customer save failed, 
	// only address is saved, otherwise both.
	transactionManager.Commit();
}
catch
{
	// fatal error, roll back everything
	transactionManager.Rollback();
	throw;
}

' VB.NET
' Create the transaction object, pass the isolation level and give it a name
Dim transactionManager As new Transaction(IsolationLevel.ReadCommitted, "SavepointRollback")

Try
	' first save a new address
	Dim newAddress As New AddressEntity()
	' ... fill the address entity with values

	transactionManager.Add(newAddress)
	
	' save it.
	newAddress.Save()

	' create a transaction save point
	transactionManager.Save("SavepointAddress")

	' save a new customer
	Dim newCustomer As New CustomerEntity()
	' ... fill the customer entity with values
	newCustomer.VisitingAddress = newAddress
	newCustomer.BillingAddress = newAddress
	
	transactionManager.Add(newCustomer)
		
	Try
		newCustomer.Save()
	Catch(Exception ex)
		' something was wrong. 
		' ... handle ex here.

		' roll back to savepoint.
		transactionManager.Rollback("SavepointAddress")
	End Try
	
	' commit the transaction. If the customer save failed, 
	' only address is saved, otherwise both.
	transactionManager.Commit()
Catch
	// fatal error, roll back everything
	transactionManager.Rollback()
	Throw
End Try


note Note:
Microsoft Access and Microsoft's Oracle ADO.NET provider don't support savepoints in transactions, so this feature is not supported when you use LLBLGen Pro with MS Access or when you use the MS Oracle provider with Oracle. In the case of Oracle, use ODP.NET instead, which does support save points.

System.Transactions support

.NET contains the System.Transactions namespace, which is a namespace with the TransactionScope class, which eases the creation of distributed transactions, by specifying a given scope. All transactions, e.g. ADO.NET transactions, are automatically elevated to distributed transactions, if required by the TransactionScope they're declared in. This requires support by the used database system as the database system has to be able to promote a non-distributed transaction to a distributed transaction.

The developer can define such a TransactionScope using the normal .NET constructs, like 

using(TransactionScope scope = new TransactionScope())
{
	// your code here.
}

An LLBLGen Pro Transaction object is able to determine if it's participating inside an ambient transaction of System.Transactions. If so, it enlists a Resource Manager with the System.Transactions transaction. The Resource manager contains the LLBLGen Pro Transaction object. As soon as a LLBLGen Pro Transaction is enlisted through a Resource Manager, the Commit() and Rollback() methods are setting the ResourceManager's commit/abort signal which is requested by the System.Transactions' Transaction manager. Once one rollback is requested, the transaction will always report a rollback to the MSDTC.
Going out of scope
When the System.Transactions transaction is committed or rolled back, the Resource manager is notified and will then notify the Transaction object (if any) that it can commit/rollback the transaction. That call will then notify the enlisted entities of the outcome of the transaction.
Example
Below is an example which shows the usage of a TransactionScope in combination of a Transaction object. The code contains Assert statements to illustrate the state / outcome of the various statements.

 // C#
CustomerEntity newCustomer = new CustomerEntity();
// fill newCustomer's fields.
// ..
AddressEntity newAddress = new AddressEntity();
// fill newAddress' fields.
// ..

// start the scope.
using( TransactionScope ts = new TransactionScope() )
{
	// start a new LLBLGen Pro transaction
	using( Transaction trans = new Transaction(System.Data.IsolationLevel.ReadCommitted, "Test") )
	{
		newCustomer.VisitingAddress = newAddress;
		newCustomer.BillingAddress = newAddress;
		// add the entities to save to the LLBLGen Pro transaction
		trans.Add( newCustomer );
		trans.Add( newAddress );
		// save both entities.
		Assert.IsTrue(newCustomer.Save( true ));
	}
	// do not call Complete, as we want to rollback the transaction and see if the rollback indeed succeeds.
	// as the TransactionScope goes out of scope, the on-going transaction is rolled back.
}
// at this point the transaction of the previous using block is rolled back.
// let the DTC and the system.transactions threads deal with the objects. 
// this sleep is only needed because we're going to access the data directly after the rollback. In normal code, 
// this sleep isn't necessary.
Thread.Sleep( 1000 );

// test if the data is still there. Shouldn't be as the transaction has been rolled back. 
CustomerEntity fetchedCustomer = new CustomerEntity( customerId );
Assert.AreEqual( EntityState.New, fetchedCustomer.Fields.State );
AddressEntity fetchedAddress = new AddressEntity( addressId);
Assert.AreEqual( EntityState.New, fetchedAddress.Fields.State );
 'VB.NET 
Dim NewCustomer As New CustomerEntity()
' fill NewCustomer's fields.
' ..
Dim NewAddress As New AddressEntity()
' fill NewAddress' fields.
' ..

' start the scope.
Using  ts As New TransactionScope() 
	' start a New LLBLGen Pro transaction
	Using trans As New Transaction(System.Data.IsolationLevel.ReadCommitted, "Test") 
		NewCustomer.VisitingAddress = NewAddress
		NewCustomer.BillingAddress = NewAddress
		' add the entities to save to the LLBLGen Pro transaction
		trans.Add( NewCustomer )
		trans.Add( NewAddress )
		' save both entities.
		Assert.IsTrue(NewCustomer.Save( True ))
	End Using
	' do not call Complete, as we want to rollback the transaction and see if the rollback indeed succeeds.
	' as the TransactionScope goes out of scope, the on-going transaction is rolled back.
End Using
' at this point the transaction of the previous using block is rolled back.
' let the DTC and the system.transactions threads deal with the objects. 
' this sleep is only needed because we're going to access the data directly after the rollback. In normal code, 
' this sleep isn't necessary.
Thread.Sleep( 1000 )

' test if the data is still there. Shouldn't be as the transaction has been rolled back. 
Dim fetchedCustomer As New CustomerEntity( customerId )
Assert.AreEqual( EntityState.New, fetchedCustomer.Fields.State )
Dim fetchedAddress As New AddressEntity( addressId)
Assert.AreEqual( EntityState.New, fetchedAddress.Fields.State )

LLBLGen Pro Runtime Framework v3.5 documentation. ©2012 Solutions Design bv